package com.java.dao.impl;

import com.java.bean.Courier;
import com.java.bean.Express;
import com.java.dao.BaseCourierDao;
import com.java.exception.DuplicateIdCardException;
import com.java.exception.DuplicatePhoneException;
import com.java.util.DruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @ClassName CourierDaoMysql
 * @Description BaseCourierDao的Mysql数据库实现
 * @Author 0715-YuHao
 * @Date 2020/9/14 15:54
 * @Version 1.0
 */
public class CourierDaoMysql extends DruidUtil implements BaseCourierDao {
    /**
     * @Author 0715-YuHao
     * @Description 用于查询数据库中的全部快递员（总数+新增）
     * @Date 15:58 2020/9/14
     **/
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(id) as size, " +
            "COUNT(TO_DAYS(signUpTime) = TO_DAYS(NOW()) OR NULL) as daySize " +
            "FROM courier";
    /**
     * @Author 0715-YuHao
     * @Description 用于查询所有快递员信息
     * @Date 18:11 2020/9/14
     **/
    private static final String SQL_FIND_ALL = "SELECT * FROM courier";
    /**
     * @Author 0715-YuHao
     * @Description 用于分页查询快递员信息
     * @Date 18:11 2020/9/14
     **/
    private static final String SQL_FIND_LIMIT = "SELECT * FROM courier LIMIT ?,?";
    /**
     * @Author 0715-YuHao
     * @Description 用于更新登录时间
     * @Date 2020/9/16 9:30
     */
    private static final String SQL_UPDATE_LOGINTIME = "UPDATE courier SET loginTime=NOW() WHERE phone=?";
    /**
     * @Author 0715-YuHao
     * @Description 通过手机号码查询快递员信息
     * @Date 18:13 2020/9/14
     **/
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM courier WHERE phone=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于录入快递员
     * @Date 18:19 2020/9/14
     **/
    private static final String SQL_INSERT = "INSERT INTO courier (name,phone,idCard,password,status,signUpTime) VALUES (?,?,?,?,0,NOW())";
    /**
     * @Author 0715-YuHao
     * @Description 用于派单量加1
     * @Date 22:40 2020/9/17
     **/
    private static final String SQL_ADD_ONE = "UPDATE courier SET numberDispatch++ WHERE phone=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于修改快递员信息
     * @Date 20:41 2020/9/14
     **/
    private static final String SQL_UPDATE = "UPDATE courier SET name=?,phone=?,idCard=?,password=?,signUpTime=Now() WHERE id=?";
    /**
     * @Author 0715-YuHao
     * @Description 用于微信端修改信息
     * @Date 10:49 2020/9/18
     **/
    private static final String SQL_UPDATE_BY_WX = "UPDATE courier SET name=?,password=? WHERE phone=?";
    /**
     * @Author 0715-YuHao
     * @Description 删除快递员
     * @Date 20:48 2020/9/14
     **/
    private static final String SQL_DELETE = "UPDATE courier SET phone=?,idCard=?,status=1 WHERE id=?";


    @Override
    public Map<String, Integer> console() {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        Map<String, Integer> data = new HashMap<>();
        try {
            statement = connection.prepareStatement(SQL_CONSOLE);
            res = statement.executeQuery();
            while (res.next()) {
                int size = res.getInt("size");
                int daySize = res.getInt("daySize");
                data.put("size", size);
                data.put("daySize", daySize);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(connection, statement, res);
        }
        return data;
    }

    @Override
    public List<Courier> findAll(boolean limit, int... args) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        List<Courier> list = new ArrayList<>();
        try {
            if (limit) {
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                statement.setInt(1, args[0]);
                statement.setInt(2, args[1]);
            }else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            res = statement.executeQuery();
            while (res.next()) {
                list.add(new Courier(
                        res.getInt("id"),
                        res.getString("name"),
                        res.getString("phone"),
                        res.getString("idCard"),
                        res.getString("password"),
                        res.getInt("numberDispatch"),
                        res.getInt("status"),
                        res.getTimestamp("signUpTime"),
                        res.getTimestamp("loginTime")
                ));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection, statement, res);
        }
        return list;
    }

    @Override
    public void updateLoginTime(String userPhone) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE_LOGINTIME);
            statement.setString(1, userPhone);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection, statement, res);
        }
    }

    @Override
    public Courier findByPhone(String phone) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        Courier courier = null;
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_PHONE);
            statement.setString(1, phone);
            res = statement.executeQuery();
            while (res.next()) {
                courier = new Courier(
                        res.getInt("id"),
                        res.getString("name"),
                        res.getString("phone"),
                        res.getString("idCard"),
                        res.getString("password"),
                        res.getInt("numberDispatch"),
                        res.getInt("status"),
                        res.getTimestamp("signUpTime"),
                        res.getTimestamp("loginTime")
                );
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection, statement, res);
        }
        return courier;
    }

    @Override
    public boolean insert(Courier courier) throws DuplicateIdCardException, DuplicatePhoneException {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement(SQL_INSERT);
            statement.setString(1, courier.getName());
            statement.setString(2, courier.getPhone());
            statement.setString(3, courier.getIdCard());
            statement.setString(4, courier.getPassword());
            return statement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            //throwables.printStackTrace();
            if (throwables.getMessage().endsWith("for key 'idCard'")) {
                throw new DuplicateIdCardException(throwables.getMessage());
            }else if (throwables.getMessage().endsWith("for key 'phone'")) {
                throw new DuplicatePhoneException(throwables.getMessage());
            }else {
                throwables.printStackTrace();
            }
        } finally {
            close(connection, statement, res);
        }
        return false;
    }

    @Override
    public boolean numberDispatchAddOne(String userPhone) {
        //1.获取数据库连接
        Connection connection = getConnection();
        PreparedStatement state = null;
        Express express = null;
        try {
            //2.预编译SQL语句
            state = connection.prepareStatement(SQL_ADD_ONE);
            state.setString(1, userPhone);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, state, null);
        }
        return false;
    }

    @Override
    public boolean update(int id, Courier newCourier) throws DuplicateIdCardException, DuplicatePhoneException {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE);
            statement.setString(1, newCourier.getName());
            statement.setString(2, newCourier.getPhone());
            statement.setString(3, newCourier.getIdCard());
            statement.setString(4, newCourier.getPassword());
            statement.setInt(5, id);
            return statement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            if (throwables.getMessage().endsWith("for key 'idCard'")) {
                throw new DuplicateIdCardException(throwables.getMessage());
            }else if (throwables.getMessage().endsWith("for key 'phone'")) {
                throw new DuplicatePhoneException(throwables.getMessage());
            }else {
                throwables.printStackTrace();
            }
        } finally {
            close(connection, statement, res);
        }
        return false;
    }

    @Override
    public boolean updateByWx(String userPhone, Courier newCourier) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement(SQL_UPDATE_BY_WX);
            statement.setString(1, newCourier.getName());
            statement.setString(2, newCourier.getPassword());
            statement.setString(3, newCourier.getPhone());
            return statement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection, statement, res);
        }
        return false;
    }

    @Override
    public boolean delete(int id, String phone, String idCard) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            phone += "#";
            idCard += "#";
            statement = connection.prepareStatement(SQL_DELETE);
            statement.setString(1, phone);
            statement.setString(2, idCard);
            statement.setInt(3, id);
            return statement.executeUpdate() > 0;
        } catch (SQLException throwables) {

        } finally {
            close(connection, statement, res);
        }
        return false;
    }
}
